#!/bin/bash

# 定义变量方便修改
table_db=cy_database 
hive=/home/software/hive-2.3.6/bin/hive

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
	do_date=$1
else 
	do_date=`date -d "-1 day" +%F`  
fi 

sql="

with
tmp1_age_desc as 
(
select
	num,amount,
	num/amount as `percent`,
	content,
	'就业人才年龄段分布' as name,
	current_date() operate_time
from(

	select
		content,num,amount
	from(
		select
			content,num,amount,
			row_number() over(partition by content) as rn
		from (
			select
				content,
				count(1) over(partition by content) as num,
				count(1) over() as amount
			from (
				SELECT
					case when age <= 20 then '00后' 
						when  20<age and age<= 30 then '90后' 
						when 30<age and age<=40 then '80后'
						when 40<age and age<=50 then '70后'
						when 50<age and age<60 then '60后'
						else '60以后' end as content
				from cy_database.dws_person_detail pwd
				)a
			)aa
	    )aaa
	where rn=1
	)b

),


with
tmp2_sex_desc as
(

select
	num,amount,
	num/amount as `percent`,
	content,
	'性别分布' as name,
	current_date() operate_time
from(
	select
		content,num,amount
	from(
		select
			content,num,amount,
			row_number() over(partition by content) as rn
		from(
			select
				content,
				count(1) over(partition by content) as num,
				count(1) over() as amount
			from(
				select
					case when sex = '0' then '女' 
						when  sex ='1' then '男' 
						else '保密' end as content	
				from cy_database.dws_person_detail
				)a
			)aa
		)aaa
	where rn=1
	)b
),


with
tmp3_education_desc as
(

select
	num,amount,
	num/amount as `percent`,
	content,
	'人才学历' as name,
	current_date() operate_time
from(

	select
		content,num,amount
	from(
		select
			content,num,amount,
			row_number() over(partition by content) as rn
		from (
			select
				content,
				count(1) over(partition by content) as num,
				count(1) over() as amount
			from (
				SELECT
					case when education = '1' or education = '2' or education = '3' then '中学及其他' 
						when  education = '4' then '专科' 
						when education = '5' then '本科'
						else '硕士级以上' end as content
				from cy_database.dws_person_detail pwd
				)a
			)aa
	    )aaa
	where rn=1
	)b

),


with
tmp4_work_amount_desc as
(

select
	count(1) as num,
	null `percent`,
	'就业总人数' as content,
	'就业总人数' as name,
	current_date() operate_time
from cy_database.dws_person_work_detail

),



with
tmp5_rc_change_desc as
(

select
	num,amount,
	num/amount as `percent`,
	content,
	'人才量变化曲线图' as name,
	current_date() operate_time
from(

	select
		content,num,amount
	from(
		select
			content,num,amount,
			row_number() over(partition by content) as rn
		from (
			select
				content,
				count(1) over(partition by content) as num,
				count(1) over() as amount
			from (
				select
					date_format(entry_work,'yyyy-MM') as content
				from cy_database.dws_enterprise_person_info
				)a
			)aa
	    )aaa
	where rn=1
	)b

),



with
tmp6_business_type_desc as
(

select
	num,amount,
	num/amount as `percent`,
	content,
	'所在行业' as name,
	current_date() operate_time
from(	
	select
	content,num,
	sum(num) over() as amount
	from(
		select
			business_code as content
			sum(posts) as num,
		from cy_database.dws_enterprise_position_require_detail
		group by business_code
		)a
	)aa
),



with
tmp7_work_year as
(

select
	num,amount,
	num/amount as `percent`,
	content,
	'工作年限' as name,
	current_date() operate_time
from(

	select
		content,num,amount
	from(
		select
			content,num,amount,
			row_number() over(partition by content) as rn
		from (
			select
				content,
				count(1) over(partition by content) as num,
				count(1) over() as amount
			from (
				SELECT
					case when work_year = '0' then '应届生' 
						when work_year = '1'  then '1年以内' 
						when work_year = '2' or work_year = '3' then '1-3年'
						when work_year = '4' or work_year = '5' then '3-5年'
						when work_year = '6' or work_year = '7' or work_year = '8' or work_year = '9' or  work_year = '10' then '5-10年'
						else '10年以上' end as content
				from cy_database.dws_enterprise_person_info
				)a
			)aa
	    )aaa
	where rn=1
	)b
),



with
tmp8_rc_position_desc  as
(

select
	num,amount,
	num/amount as `percent`,
	content,
	'人才职业分布' as name,
	current_date() operate_time
from(

	select
		content,num,amount
	from(
		select
			content,num,amount,
			row_number() over(partition by content) as rn
		from (
			select
				content,
				count(1) over(partition by content) as num,
				count(1) over() as amount
			from (
				SELECT
					case when position_code = 100 then '政府雇员' 
						when  position_code = 101 then '人事' 
						when position_code = 102 then '计算机'
						when position_code = 103 then '销售'
						when position_code = 104 then '市场'
						when position_code = 105 then '财税'
						when position_code = 106 then '生产'
						when position_code = 107 then '生物'
						when  position_code = 108 then '建筑' 
						when position_code = 109 then '中介'
						when position_code = 110 then '出版'
						when position_code = 111 then '轻工'
						when position_code = 112 then '勘探'
						when  position_code = 113 then '能源' 
						when position_code = 114 then '餐饮'
						when position_code = 115 then '机械/汽车'
						when position_code = 116 then '生活服务'
						when position_code = 117 then '市政'
						when  position_code = 118 then '技工' 
						else '其他' end as content
				from cy_database.dws_person_work_detail
				)a
			)aa
	    )aaa
	where rn=1
	)b
),

insert into table ads_big_screen_result_rc
select num,`percent`,content,name,operate_time from tmp1_age_desc

insert into table ads_big_screen_result_rc
select num,`percent`,content,name,operate_time from tmp2_sex_desc

insert into table ads_big_screen_result_rc
select num,`percent`,content,name,operate_time from tmp3_education_desc

insert into table ads_big_screen_result_rc
select num,`percent`,content,name,operate_time from tmp4_work_amount_desc

insert into table ads_big_screen_result_rc
select num,`percent`,content,name,operate_time from tmp5_rc_change_desc

insert into table ads_big_screen_result_rc
select num,`percent`,content,name,operate_time from tmp6_business_type_desc

insert into table ads_big_screen_result_rc
select num,`percent`,content,name,operate_time from tmp7_work_year

insert into table ads_big_screen_result_rc
select num,`percent`,content,name,operate_time from tmp8_rc_position_desc;



"
$hive -e "$sql"